import pymysql
from traceback import print_exc     # 打印报错的堆栈信息
class DB:
    def __init__(self,host,user,password,database):
        """
        :param host: 传入要链接的ip地址
        :param user: 传入账号
        :param password: 传入密码
        :param database: 传入数据库名称
        """
        self.host = host
        self.user = user
        self.database = database
        self.password = password
    def connect(self):

        # 创建db对象
        self.db = pymysql.connect(host=self.host,user = self.user,password=self.password,database=self.database,cursorclass =pymysql.cursors.DictCursor)
        # 创建游标
        self.cursor = self.db.cursor()


    def close(self):
        self.cursor.close()
        self.db.close()
    def searchOne(self,sql):
        result = None
        self.connect()
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchone()
            self.close()
            return result
        except:
            self.db.rollback()      # 如果出现了报错，数据库回滚
            return result
    def searchAll(self,sql):
        result = None
        self.connect()
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.close()
            return result
        except:
            self.db.rollback()  # 如果出现了报错，数据库回滚
            return result
    def __edit(self,sql):   # 主程序
        self.connect()      # 先连接数据库
        try:
            self.cursor.execute(sql)        # 执行sql
            self.db.commit()        # 提交对数据库的改动
            self.close()        # 关闭数据库
        except:
            self.db.rollback()      # 如果出现异常，数据库回滚
            print_exc()     # 打印错误的堆栈信息
    def insert(self,sql):
        self.__edit(sql)
    def delete(self,sql):
        self.__edit(sql)
    def update(self,sql):
        self.__edit(sql)
if __name__ == '__main__':
    # 创建d1对象
    d1 = DB(host = '47.97.51.55' ,user = 'root',password='123456',database='jiac')
    """
    # 调用查询方法
    print(d1.searchAll("select * from student"))
    """
    """
    # 调用新增方法
    d1.insert("insert into student(name) values('王五')")
    """
    """
    # 调用查询方法
    print(d1.searchOne("select * from student where name='王五'"))
    """
    """
    # 调用删除方法
    d1.delete('delete from student where name="王五"')

    # 查询所有
    print(d1.searchAll("select * from student"))
    """

    """
    # 调用更新方法
    d1.update('update student set name="长2" where name="张二"')

    # 查询所有
    print(d1.searchAll("select * from student"))
    """
    pass